<?php
namespace mvc\web;

class Db 
{
    public $pdo;
    public $dsn;
    public $username;
    public $password;
    public $attribute;
    public $charset;

    public $from;
    public $where;
    public $limit;
    public $orderBy;
    public $select;
    public $offset;
    public $groupBy;
    public $join;

    public function open()
    {
        $this->pdo = new \PDO($this->dsn, $this->username, $this->password, $this->attribute);
        $this->pdo->exec("set names '{$this->charset}'");
    }

    public function from($table)
    {
        $this->from = $table;
    }

    public function where($where)
    {
        $this->where = $where;
    }

    public function limit($limit)
    {
        $this->limit = $limit;
    }

    public function orderBy($orderBy)
    {
        $this->orderBy = $orderBy;
    }

    public function select($select)
    {
        $this->select = $select;
    }

    public function groupBy($groupBy)
    {
        $this->groupBy = $groupBy;
    }

    public function join($tableName, $join, $joinType='left join')
    {
        $this->join = [$tableName, $join, $joinType];
    }
    
    public function all()
    {
        $sql = $this->buildSql();
        $rs = $this->pdo->query($sql);

        if($rs == false) {
            return [];
        }
        $result = $rs->fetchAll(\PDO::FETCH_ASSOC);
        return $result;
    }

    public function one()
    {
        $this->limit = 1;
        $sql = $this->buildSql();
        $rs = $this->pdo->query($sql);

        if($rs == false) {
            return [];
        }

        $result = $rs->fetch(\PDO::FETCH_ASSOC);
        return $result;
    }

    public function buildSql()
    {
        $sql = "select ";

        if(empty($this->select)) {
            $this->select = '*';
        }

        $sql .= "{$this->select} from {$this->from} ";

        if(!empty($this->join)) {
            $tableName = $this->join[0];
            $join = $this->join[1];
            $joinType = $this->join[2];
            $sql .= "$joinType $tableName $join ";
        }

        if(!empty($this->where)) {
            $sql .= "where {$this->where} ";
        }

        if(!empty($this->orderBy)) {
            $sql .= "order by {$this->orderBy} ";
        }

        if($this->limit !== null) {
            if ($this->offset === null) {
                $sql .= "limit {$this->limit} ";
            } else {
                $sql .= "limit {$this->offset},{$this->limit} ";
            }
        }

        if(!empty($this->groupBy)) {
            $sql .= "group by {$this->groupBy}";
        }

        return $sql;
    }

    public function showColumns($tableName)
    {
        $result = [];
        $rs = $this->pdo->query("SHOW COLUMNS FROM " . $tableName);
        $columns = $rs->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($columns as $key => $item) {
            $result[] = $item['Field'];
        }
        return $result;
    }

    public function insert($tableName, $data)
    {
        $keyStr = "insert into {$tableName} (";
        $key = array_keys($data);
        foreach ($key as $k) {
            $keyStr .= "`$k`" . ',';
        }
        $keyStr = substr($keyStr, 0, -1);
        $keyStr .= ')';

        $valueStr = ' values (';
        $value = array_values($data);
        foreach ($value as $k) {
            $valueStr .= "'$k'" . ',';
        }
        $valueStr = substr($valueStr, 0, -1);
        $valueStr .= ')';

        $sql = $keyStr . $valueStr;
        return $this->pdo->exec($sql);
    }

    public function update($tableName, $data)
    {
        if(empty($this->where)) {
            return false;
        }

        $sql = "update {$tableName} set ";
        foreach ($data as $key => $item) {
            $sql .= "`$key`='$item',";
        }
        $sql = substr($sql, 0, -1);

        $sql .= "where {$this->where}";

        return $this->pdo->exec($sql);
    }

    public function delete($tableName)
    {
        if(empty($this->where)) {
            return false;
        }

        $sql = "delete from {$tableName} where {$this->where}";

        return $this->pdo->exec($sql);
    }

    public function count($tableName)
    {
        if($this->where) {
            $sql = "select count(*) as count from {$tableName} where {$this->where}";
        } else {
            $sql = "select count(*) as count from {$tableName}";
        }

        $res = $this->pdo->query($sql);
        return $res->fetchObject()->count;
    }

    public function __destruct()
    {
        $this->pdo = null;
    }
}